﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE
Public Class InformeMensualDA

    Public Function ListaInformeAdministrador(ByVal strPeriodo As String, ByVal strAnhioFirma As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        'Dim lxc_SQL As String
        Try
            objData.CreateInputOracle("v_Periodo", strPeriodo, 6)
            objData.CreateInputOracle("v_Ano", strAnhioFirma, 4)
            objData.CreateOutputOracle("p_Cursor", "", 100)
            objDt = objData.ExecuteOracle("SP_LISTA_INFORMES_ADMIN", "Conexion.Oracle")

            'lxc_SQL = "SELECT DISTINCT PMM.ID_INFORME_MENSUAL AS IDIM, P.POA AS COD_POA,P.NOMBRE,P.PERSONA_MONITOREO AS COD_PERSONA,PERS.NOMBRES|| ' ' ||PERS.APELLIDO_PATERNO|| ' ' ||PERS.APELLIDO_MATERNO AS MONITOREADOR, PMM.FECHA,G.DESCRIPCION AS ESTADO, " & _
            '            "PP.PPER AS CPPER,PP.ABREVIATURA AS DPPER,PERS.OFICINA_ZONAL,OZ.DESCRIPCION AS OZ,P.EJECUTOR AS CEJECUTOR,EJ.DESCRIPCION AS DEJECUTOR,P.SNIP,P.POA,PMM.PERIODO, P.VERSION VERSION_POA_VIGENTE,  PMM.VERSION VERSION_POA_INFORME, VERIFICA_PROY_CONCLUIDO(P.POA, P.PERIODO_FIN_PROY, '" & strPeriodo & "') VERIF_EST_PROY, " & _
            '            "VERIFICA_SEG_FIN_TERMINADO(P.POA, '" & strPeriodo & "') VER_FIN, VERIFICA_SEG_FIS_TERMINADO(P.POA, '" & strPeriodo & "') VER_FIS " & _
            '            "FROM POA P " & _
            '            "LEFT JOIN POA_MENSUAL_MONITOREO PMM   ON P.POA=PMM.POA AND PMM.PERIODO='" & strPeriodo & "' " & _
            '            "INNER JOIN PERSONA PERS ON PERS.PERSONA=P.PERSONA_MONITOREO " & _
            '            "LEFT JOIN GENERAL G ON G.ID= 'ESTADO_INFORME_MENSUAL' AND G.CODIGO = PMM.ESTADO " & _
            '            "INNER JOIN OFICINA_ZONAL OZ ON OZ.OFICINA_ZONAL=PERS.OFICINA_ZONAL " & _
            '            "INNER JOIN PPER PP ON PP.PPER=PERS.PPER " & _
            '            "LEFT JOIN EJECUTOR EJ ON EJ.EJECUTOR=P.EJECUTOR " & _
            '            "WHERE ANHIO_FIRMA_POA = '" & strAnhioFirma & "' " & _
            '            "ORDER BY EJ.DESCRIPCION ASC, P.NOMBRE ASC "

            'objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaInformeMonitoreador(ByVal strPersona As String, ByVal strPeriodo As String, ByVal strAnhioFirma As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        ' Dim lxc_SQL As String
        Try
            objData.CreateInputOracle("v_Persona", strPersona, 8)
            objData.CreateInputOracle("v_Periodo", strPeriodo, 6)
            objData.CreateInputOracle("v_Ano", strAnhioFirma, 4)
            objData.CreateOutputOracle("p_Cursor", "", 100)
            objDt = objData.ExecuteOracle("SP_LISTA_INFORMES_MONITOREADOR", "Conexion.Oracle")

            'lxc_SQL = "SELECT DISTINCT PMM.ID_INFORME_MENSUAL AS IDIM, P.POA AS COD_POA,P.NOMBRE,P.PERSONA_MONITOREO AS COD_PERSONA,PERS.NOMBRES|| ' ' ||PERS.APELLIDO_PATERNO|| ' ' ||PERS.APELLIDO_MATERNO AS MONITOREADOR, PMM.FECHA,G.DESCRIPCION AS ESTADO, PP.PPER AS CPPER,PP.ABREVIATURA AS DPPER,PERS.OFICINA_ZONAL,OZ.DESCRIPCION AS OZ,P.EJECUTOR AS CEJECUTOR,EJ.DESCRIPCION AS DEJECUTOR,P.SNIP,P.POA,PMM.PERIODO, P.VERSION VERSION_POA_VIGENTE, PMM.VERSION VERSION_POA_INFORME, VERIFICA_PROY_CONCLUIDO(P.POA, P.PERIODO_FIN_PROY, '" & strPeriodo & "') VERIF_EST_PROY ," & _
            '          "VERIFICA_SEG_FIN_TERMINADO(P.POA, '" & strPeriodo & "') VER_FIN, VERIFICA_SEG_FIS_TERMINADO(P.POA, '" & strPeriodo & "') VER_FIS " & _
            '          "FROM POA P " & _
            '          "LEFT JOIN POA_MENSUAL_MONITOREO PMM   ON P.POA=PMM.POA AND PMM.PERIODO='" & strPeriodo & "' " & _
            '          "INNER JOIN PERSONA PERS ON PERS.PERSONA=P.PERSONA_MONITOREO " & _
            '          "LEFT JOIN GENERAL G ON G.ID= 'ESTADO_INFORME_MENSUAL' AND G.CODIGO = PMM.ESTADO " & _
            '          "INNER JOIN OFICINA_ZONAL OZ ON OZ.OFICINA_ZONAL=PERS.OFICINA_ZONAL  " & _
            '          "INNER JOIN PPER PP ON PP.PPER=PERS.PPER  " & _
            '          "LEFT JOIN EJECUTOR EJ ON EJ.EJECUTOR=P.EJECUTOR  " & _
            '          "INNER JOIN (SELECT DISTINCT PERSONA_MONITOREO,PERSONA_ESPECIALISTA  FROM POA WHERE '" & strPersona & "' IN (PERSONA_MONITOREO,PERSONA_ESPECIALISTA) ) O ON O.PERSONA_MONITOREO = PERS.PERSONA " & _
            '          "WHERE '" & strPersona & "' IN (PERS.PERSONA,PERS.JEFE_INMEDIATO) AND ANHIO_FIRMA_POA = '" & strAnhioFirma & "' " & _
            '          "ORDER BY EJ.DESCRIPCION ASC, P.NOMBRE ASC "
            'objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaInformeEspecialista(ByVal strPersEspecialista As String, ByVal strPeriodo As String, ByVal strAnhioFirma As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        'Dim lxc_SQL As String
        Try
            objData.CreateInputOracle("v_Persona", strPersEspecialista, 8)
            objData.CreateInputOracle("v_Periodo", strPeriodo, 6)
            objData.CreateInputOracle("v_Ano", strAnhioFirma, 4)
            objData.CreateOutputOracle("p_Cursor", "", 100)
            objDt = objData.ExecuteOracle("SP_LISTA_INFORMES", "Conexion.Oracle")


            'lxc_SQL = "SELECT DISTINCT PMM.ID_INFORME_MENSUAL AS IDIM, P.POA AS COD_POA,P.NOMBRE,P.PERSONA_MONITOREO AS COD_PERSONA,PERS.NOMBRES|| ' ' ||PERS.APELLIDO_PATERNO|| ' ' ||PERS.APELLIDO_MATERNO AS MONITOREADOR, PMM.FECHA,G.DESCRIPCION AS ESTADO, PP.PPER AS CPPER,PP.ABREVIATURA AS DPPER,PERS.OFICINA_ZONAL,OZ.DESCRIPCION AS OZ,P.EJECUTOR AS CEJECUTOR,EJ.DESCRIPCION AS DEJECUTOR,P.SNIP,P.POA,PMM.PERIODO, P.VERSION VERSION_POA_VIGENTE,  PMM.VERSION VERSION_POA_INFORME, VERIFICA_PROY_CONCLUIDO(P.POA, P.PERIODO_FIN_PROY, '" & strPeriodo & "') VERIF_EST_PROY, " & _
            '          "VERIFICA_SEG_FIN_TERMINADO(P.POA, '" & strPeriodo & "') VER_FIN, VERIFICA_SEG_FIS_TERMINADO(P.POA, '" & strPeriodo & "') VER_FIS " & _
            '          "FROM POA P " & _
            '          "LEFT JOIN POA_MENSUAL_MONITOREO PMM   ON P.POA=PMM.POA AND PMM.PERIODO='" & strPeriodo & "' " & _
            '          "INNER JOIN PERSONA PERS ON PERS.PERSONA=P.PERSONA_MONITOREO " & _
            '          "LEFT JOIN GENERAL G ON G.ID= 'ESTADO_INFORME_MENSUAL' AND G.CODIGO = PMM.ESTADO " & _
            '          "INNER JOIN OFICINA_ZONAL OZ ON OZ.OFICINA_ZONAL=PERS.OFICINA_ZONAL  " & _
            '          "INNER JOIN PPER PP ON PP.PPER=PERS.PPER  " & _
            '          "LEFT JOIN EJECUTOR EJ ON EJ.EJECUTOR=P.EJECUTOR  " & _
            '          "WHERE '" & strPersEspecialista & "' IN (P.PERSONA_ESPECIALISTA) AND ANHIO_FIRMA_POA = '" & strAnhioFirma & "' " & _
            '          "ORDER BY EJ.DESCRIPCION ASC, P.NOMBRE ASC "
            'objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaInformeJefeInmmediato(ByVal strJefeInmediato As String, ByVal strPeriodo As String, ByVal strAnhioFirma As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        'Dim lxc_SQL As String
        Try
            objData.CreateInputOracle("v_Persona", strJefeInmediato, 8)
            objData.CreateInputOracle("v_Periodo", strPeriodo, 6)
            objData.CreateInputOracle("v_Ano", strAnhioFirma, 4)
            objData.CreateOutputOracle("p_Cursor", "", 100)
            objDt = objData.ExecuteOracle("SP_LISTA_INFORMES_JO", "Conexion.Oracle")
            'lxc_SQL = "SELECT DISTINCT PMM.ID_INFORME_MENSUAL AS IDIM, P.POA AS COD_POA,P.NOMBRE,P.PERSONA_MONITOREO AS COD_PERSONA,PERS.NOMBRES|| ' ' ||PERS.APELLIDO_PATERNO|| ' ' ||PERS.APELLIDO_MATERNO AS MONITOREADOR, PMM.FECHA,G.DESCRIPCION AS ESTADO, " & _
            '            "PP.PPER AS CPPER,PP.ABREVIATURA AS DPPER,PERS.OFICINA_ZONAL,OZ.DESCRIPCION AS OZ,P.EJECUTOR AS CEJECUTOR,EJ.DESCRIPCION AS DEJECUTOR,P.SNIP,P.POA,PMM.PERIODO, P.VERSION VERSION_POA_VIGENTE,  PMM.VERSION VERSION_POA_INFORME, VERIFICA_PROY_CONCLUIDO(P.POA, P.PERIODO_FIN_PROY, '" & strPeriodo & "') VERIF_EST_PROY, " & _
            '            "VERIFICA_SEG_FIN_TERMINADO(P.POA, '" & strPeriodo & "') VER_FIN, VERIFICA_SEG_FIS_TERMINADO(P.POA, '" & strPeriodo & "') VER_FIS " & _
            '            "FROM POA P " & _
            '            "LEFT JOIN POA_MENSUAL_MONITOREO PMM   ON P.POA=PMM.POA AND PMM.PERIODO='" & strPeriodo & "' " & _
            '            "INNER JOIN PERSONA PERS ON PERS.PERSONA=P.PERSONA_MONITOREO " & _
            '            "LEFT JOIN GENERAL G ON G.ID= 'ESTADO_INFORME_MENSUAL' AND G.CODIGO = PMM.ESTADO " & _
            '            "INNER JOIN OFICINA_ZONAL OZ ON OZ.OFICINA_ZONAL=PERS.OFICINA_ZONAL " & _
            '            "INNER JOIN PPER PP ON PP.PPER=PERS.PPER " & _
            '            "LEFT JOIN EJECUTOR EJ ON EJ.EJECUTOR=P.EJECUTOR " & _
            '            "INNER JOIN (SELECT PERSONA PERSONA_SUBORDINADO FROM PERSONA WHERE JEFE_INMEDIATO = '" & strJefeInmediato & "' ) PER_SUB ON PER_SUB.PERSONA_SUBORDINADO = P.PERSONA_MONITOREO " & _
            '            "WHERE ANHIO_FIRMA_POA = '" & strAnhioFirma & "' " & _
            '            "ORDER BY EJ.DESCRIPCION ASC, P.NOMBRE ASC "

            'objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class
